package repository

import (
	"gorm.io/gorm"
	"shop-bean/model"
	"shop-bean/vo"
)

type IShippingTemplatesFreeRepository interface {
	FindGroup(db *gorm.DB, tempId uint) ([]*vo.ShippingTemplatesFreeVO, error)
	BatchDelete(db *gorm.DB, tempIds []uint) error
}

type ShippingTemplatesFreeRepository struct {
}

func NewShippingTemplatesFreeRepository() IShippingTemplatesFreeRepository {
	return &ShippingTemplatesFreeRepository{}
}

func (r *ShippingTemplatesFreeRepository) BatchDelete(db *gorm.DB, tempIds []uint) error {
	return db.Where("temp_id IN ?", tempIds).Where("status = 1").Delete(&model.ShippingTemplatesFree{}).Error
}

func (r *ShippingTemplatesFreeRepository) FindGroup(db *gorm.DB, tempId uint) (rows []*vo.ShippingTemplatesFreeVO, err error) {
	return rows, db.Raw(`
		SELECT
			g.uniq_id,
			g.province_id,
			g.city_id_str,
			sys_city. NAME name_str,
			g.number,
			g.price
		FROM
			(
				SELECT
					t.uniq_id,
					GROUP_CONCAT(DISTINCT t.city_id) AS city_id_str,
					t.province_id,
					t.number,
					t.price
				FROM
					(
						SELECT
							province_id,
							city_id,
							uniq_id,
							number,
							price
						FROM
							tz_shipping_templates_free
						WHERE
							temp_id = ?
					) t
				GROUP BY
					t.uniq_id,
					t.province_id,
					t.number,
					t.price
			) g
		LEFT JOIN tz_sys_city sys_city ON g.province_id = sys_city.city_id
	`, tempId).Find(&rows).Error
}

/*

SELECT
	g.uniq_id,
	g.province_id,
	g.city_id_str,
	sys_city. NAME name_str
FROM
	(
		SELECT
			t.uniq_id,
			GROUP_CONCAT(DISTINCT t.city_id) AS city_id_str,
			t.province_id
		FROM
			(
				SELECT
					province_id,
					city_id,
					uniq_id,
number,
price
				FROM
					tz_shipping_templates_free
				WHERE
					temp_id = 3
			) t
		GROUP BY
			t.uniq_id,
			t.province_id
	) g
LEFT JOIN tz_sys_city sys_city ON g.province_id = sys_city.city_id
*/
